package com.manager.plat.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.IConversionDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.AnalysisVo;
import com.manager.plat.vo.ConversionVo;
import com.manager.plat.vo.HangupRateVo;
import com.manager.plat.vo.UserCallinVo;
/**
 * 转化率数据实现层
 * @author liugeng
 *
 */
public class ConversionDaoImpl extends JdbcDaoSupport implements IConversionDao {
	private static Logger logger = Logger.getLogger(ConversionDaoImpl.class);
	
	/**根据查询条件获得转化率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<ConversionVo> getConversion(int lower_id, String sp_number, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();

//		String sql = "SELECT callin.sp_number, date_format(callin.add_date,'%Y-%m-%d') add_date, ifnull(bill.lower_id,0) group_lower_id, ifnull(router.lower_id,0) lower_id, ifnull(router.`name`, '') lower_name, count(0) fee_number " +
//					"FROM tb_ivr_user_callin callin "+
//					"INNER JOIN tb_ivr_config_router router on router.sp_number = callin.sp_number "+
//					"INNER JOIN tb_ivr_config_lower_partner partner on partner.id = router.lower_id "+
//					"LEFT JOIN tb_ivr_user_bill bill on callin.linkId = bill.linkId "+
//					"WHERE callin.add_date >= ? and callin.add_date <= ? ";
		
		String sql = "SELECT date_format(callin_table.add_date,'%Y-%m-%d') add_date, ifnull(router.`name`, '') lower_name, router.lower_id, callin_table.sp_number sp_number, callin_table.callin_number, ifnull(bill_table.bill_number,0) bill_number " +
					"from ( " +
					"select callin.add_date, callin.sp_number, count(0) callin_number " +
					"from tb_ivr_user_callin callin "+
					"where callin.add_date >= ? and callin.add_date <= ? "+
					"group by callin.sp_number) callin_table "+
					"left join ( "+
					"select bill.add_date, bill.sp_number, count(0) bill_number "+
					"from tb_ivr_user_bill bill "+
					"where bill.add_date >= ? and bill.add_date <= ? "+
					"group by bill.lower_id, bill.sp_number) bill_table "+
					"on bill_table.sp_number = callin_table.sp_number "+
					"inner join tb_ivr_config_router router on router.sp_number = callin_table.sp_number "+
					"inner join tb_ivr_config_lower_partner partner on partner.id = router.lower_id "+
					"where callin_table.add_date >= ? and callin_table.add_date <= ?";
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(time_begin);
		paramList.add(time_end);
		if(lower_id != 0){
			sql += "and lower_id = ? ";
			paramList.add(lower_id);
		}
		if(!"".equals(sp_number)){
			sql += "and sp_number = ? ";
			paramList.add(sp_number);
		}
			 sql += "order by add_date, sp_number, lower_id";
			 
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得转化率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		
		try{
			List<ConversionVo> conversionVoList = this.getJdbcTemplate().query(sql, params, rowMapper);
			if(conversionVoList != null && conversionVoList.size() > 0){
				return conversionVoList;
			}
		}catch(Exception e){
			logger.info(e.getMessage());
			logger.info(ExceptionUtils.getFullStackTrace(e));
			e.printStackTrace();
		}
		
		return null;
	}
	
	private ParameterizedRowMapper<ConversionVo> rowMapper = new ParameterizedRowMapper<ConversionVo>() {

		@Override
		public ConversionVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ConversionVo conversionVo = new ConversionVo();
			conversionVo.setSp_number(rs.getString("sp_number"));
			conversionVo.setAdd_date(rs.getString("add_date"));
			conversionVo.setLower_id(rs.getInt("lower_id"));
			conversionVo.setLower_name(rs.getString("lower_name"));
			conversionVo.setFee_number(rs.getInt("bill_number"));
			conversionVo.setUser_num(rs.getInt("callin_number"));
			conversionVo.setNo_fee_number(rs.getInt("callin_number") - rs.getInt("bill_number"));
			return conversionVo;
		}
	};
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ConversionVo> getConversionPartner(int user_id, String sp_number, String time_begin, String time_end) {
		List<Object> paramList = new ArrayList<Object>();
		String sql = "select tc.sp_number, date_format(tc.add_date,'%Y-%m-%d') add_date, ifnull(tb.lower_id,0) group_lower_id, ifnull(tc.lower_id,0) lower_id, ifnull(tc.lower_name, '') lower_name, count(0) fee_number " +
				 "from (" +
				 " select tc.mobile, tc.sp_number, tc.linkId, tp.id lower_id, tp.name lower_name, tc.add_date " +
				 " from tb_ivr_user_callin tc " +
				 " inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number " +
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id " +
				 " where tc.add_date >= ? and tc.add_date <= ? and tp.user_id = ? ";
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(user_id);
		if(!"".equals(sp_number)){
			sql += "and tc.sp_number = ? ";
			paramList.add(sp_number);
		}
				 
		 sql += " and tc.isfilter = 0 ) tc " +
				 "left join (" +
				 " select tb.mobile, tb.sp_number, tb.linkId, tb.lower_id, tp.name lower_name " +
				 " from tb_ivr_user_bill tb " + 
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id " +
				 " where tb.add_date >= ? and tb.add_date <= ? and tp.user_id = ? ";
		paramList.add(time_begin);
		paramList.add(time_end);
		paramList.add(user_id);
		if(!"".equals(sp_number)){
			sql += "and tb.sp_number = ? ";
			paramList.add(sp_number);
		}
		 sql += " and tb.isfilter = 0 ) tb " +
				 "on tb.linkId = tc.linkId " +
				 "group by tc.sp_number, date_format(tc.add_date,'%Y-%m-%d'), tb.lower_id " +
				 "order by date_format(tc.add_date,'%Y-%m-%d')";
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得转化率伙伴信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<ConversionVo> conversionVoList = this.getJdbcTemplate().query(sql, params, rowMapper);
		if(conversionVoList != null && conversionVoList.size() > 0){
			return conversionVoList;
		}
		
		return null;
	}
	
	/**根据查询条件获得转化率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<ConversionVo> getDetailConversion(int lower_id, String sp_number, String time_begin, String time_end) {
		String sql = "select tc.sp_number, date_format(tc.add_date,'%Y-%m-%d %H') add_date, ifnull(tb.lower_id,0) group_lower_id, ifnull(tc.lower_id,0) lower_id, ifnull(tc.lower_name, '') lower_name, count(0) fee_number " +
					 "from (" +
					 " select tc.mobile, tc.sp_number, tc.linkId, tp.id lower_id, tp.name lower_name, tc.add_date " +
					 " from tb_ivr_user_callin tc " +
					 " inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number " +
					 " inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id " +
					 " where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.id = ? " +
					 ") tc " +
					 "left join (" +
					 " select tb.mobile, tb.sp_number, tb.linkId, tb.lower_id, tp.name lower_name " +
					 " from tb_ivr_user_bill tb " + 
					 " inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id " +
					 " where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tb.lower_id = ? " +
					 ") tb " +
					 "on tb.linkId = tc.linkId " +
					 "group by tc.sp_number, date_format(tc.add_date,'%Y-%m-%d %H'), tb.lower_id " +
					 "order by date_format(tc.add_date,'%Y-%m-%d %H')";
		Object[] params = new Object[]{time_begin, time_end, sp_number, lower_id, time_begin, time_end, sp_number, lower_id};
		logger.info(String.format("根据查询条件获得转化率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<ConversionVo> conversionVoList = this.getJdbcTemplate().query(sql, params, rowMapper);
		if(conversionVoList != null && conversionVoList.size() > 0){
			return conversionVoList;
		}
		
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ConversionVo> getDetailConversionPartner(int user_id, String sp_number, String time_begin, String time_end) {
		String sql = "select tc.sp_number, date_format(tc.add_date,'%Y-%m-%d %H') add_date, ifnull(tb.lower_id,0) group_lower_id, ifnull(tc.lower_id,0) lower_id, ifnull(tc.lower_name, '') lower_name, count(0) fee_number " +
				 "from (" +
				 " select tc.mobile, tc.sp_number, tc.linkId, tp.id lower_id, tp.name lower_name, tc.add_date " +
				 " from tb_ivr_user_callin tc " +
				 " inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number " +
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id " +
				 " where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.user_id = ? and tc.isfilter = 0 " +
				 ") tc " +
				 "left join (" +
				 " select tb.mobile, tb.sp_number, tb.linkId, tb.lower_id, tp.name lower_name " +
				 " from tb_ivr_user_bill tb " + 
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id " +
				 " where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 " +
				 ") tb " +
				 "on tb.linkId = tc.linkId " +
				 "group by tc.sp_number, date_format(tc.add_date,'%Y-%m-%d %H'), tb.lower_id " +
				 "order by date_format(tc.add_date,'%Y-%m-%d %H')";
		Object[] params = new Object[]{time_begin, time_end, sp_number, user_id, time_begin, time_end, sp_number, user_id};
		logger.info(String.format("根据查询条件获得转化率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<ConversionVo> conversionVoList = this.getJdbcTemplate().query(sql, params, rowMapper);
		if(conversionVoList != null && conversionVoList.size() > 0){
			return conversionVoList;
		}
		
		return null;
	}
	
	/**根据查询条件获得呼入信息详情*/
	@SuppressWarnings("unchecked")
	@Override
	public List<UserCallinVo> getUserCallin(int lower_id, String sp_number, String time_begin, String time_end, String add_date) {
		String sql = "select tc.mobile, tc.sp_number, tc.province, tc.city, tc.state_code, date_format(tc.add_date,'%Y-%m-%d %H:%i:%s') add_date, ifnull(tc.lower_id,0) lower_id, ifnull(tc.lower_name, '') lower_name " +
				 "from (" +
				 " select tc.mobile, tc.sp_number, tc.province, tc.city, tc.state_code, tc.add_date, tp.id lower_id, tp.name lower_name, tc.linkId " +
				 " from tb_ivr_user_callin tc " +
				 " inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number " +
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id " +
				 " where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.id = ? " +
				 ") tc " +
				 "left join (" +
				 " select tb.linkId " +
				 " from tb_ivr_user_bill tb " + 
				 " where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tb.lower_id = ? " +
				 ") tb " +
				 "on tb.linkId = tc.linkId " +
				 "where tb.linkId is null and date_format(tc.add_date,'%Y-%m-%d %H') = ?";
		Object[] params = new Object[]{time_begin, time_end, sp_number, lower_id, time_begin, time_end, sp_number, lower_id, add_date};
		logger.info(String.format("根据查询条件获得呼入信息详情[%s]", BaseUtil.logSQL(sql, params)));
		
		List<UserCallinVo> userCallinVoList = this.getJdbcTemplate().query(sql, params, userCallinMapper);
		if(userCallinVoList != null && userCallinVoList.size() > 0){
			return userCallinVoList;
		}
		
		return null;
	}
	/**根据查询条件获得呼入信息详情*/
	@SuppressWarnings("unchecked")
	@Override
	public List<UserCallinVo> getUserCallinPartner(int user_id, String sp_number, String time_begin, String time_end, String add_date) {
		String sql = "select tc.mobile, tc.sp_number, tc.province, tc.city, tc.state_code, date_format(tc.add_date,'%Y-%m-%d %H:%i:%s') add_date, ifnull(tc.lower_id,0) lower_id, ifnull(tc.lower_name, '') lower_name " +
				 "from (" +
				 " select tc.mobile, tc.sp_number, tc.province, tc.city, tc.state_code, tc.add_date, tp.id lower_id, tp.name lower_name, tc.linkId " +
				 " from tb_ivr_user_callin tc " +
				 " inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number " +
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id " +
				 " where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.user_id = ? and tc.isfilter = 0 " +
				 ") tc " +
				 "left join (" +
				 " select tb.linkId " +
				 " from tb_ivr_user_bill tb " + 
				 " inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id " +
				 " where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 " +
				 ") tb " +
				 "on tb.linkId = tc.linkId " +
				 "where tb.linkId is null and date_format(tc.add_date,'%Y-%m-%d %H') = ?";
		Object[] params = new Object[]{time_begin, time_end, sp_number, user_id, time_begin, time_end, sp_number, user_id, add_date};
		logger.info(String.format("根据查询条件获得呼入信息详情[%s]", BaseUtil.logSQL(sql, params)));
		
		List<UserCallinVo> userCallinVoList = this.getJdbcTemplate().query(sql, params, userCallinMapper);
		if(userCallinVoList != null && userCallinVoList.size() > 0){
			return userCallinVoList;
		}
		
		return null;
	}
	
	private ParameterizedRowMapper<UserCallinVo> userCallinMapper = new ParameterizedRowMapper<UserCallinVo>() {
		@Override
		public UserCallinVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			UserCallinVo userCallinVo = new UserCallinVo();
			userCallinVo.setMobile(rs.getString("mobile"));
			userCallinVo.setSp_number(rs.getString("sp_number"));
			userCallinVo.setProvince(rs.getString("province"));
			userCallinVo.setCity(rs.getString("city"));
			userCallinVo.setState_code(rs.getInt("state_code"));
			userCallinVo.setAdd_date(rs.getString("add_date"));
			userCallinVo.setLower_id(rs.getInt("lower_id"));
			userCallinVo.setLower_name(rs.getString("lower_name"));
			return userCallinVo;
		}
	};
	/**根据查询条件获得数据分析信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<AnalysisVo> getProvinceAnalysis(String sp_number, int lower_id, String time_begin, String time_end, String add_date) {
		String sql = "select tb.province, tb.isfilter, count(1) fee_user_num, sum(tb.minute) fee_minute, " 
					+"ceiling(sum(case when tb.fee_type = 1 then (tb.minute * tb.fee_code/100) when tb.fee_type = 2 then (tb.fee_code/100) else 0 end)) fee_price "
					+"from ("
					+" select tc.add_date, tc.linkId "
					+" from tb_ivr_user_callin tc "
					+" inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number "
					+" inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id "
					+" where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.id = ? "
					+") tc "
					+"left join ("
					+" select tb.province, tb.isfilter, tb.minute, tcb.fee_type, tcb.fee_code, tb.linkId "
					+" from tb_ivr_user_bill tb "
					+" inner join tb_ivr_config_router tr on tr.id = tb.router_id "
					+" inner join tb_ivr_config_business tcb on tcb.id = tr.sp_code_id "
					+" where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tb.lower_id = ? "
					+") tb "
					+"on tb.linkId = tc.linkId "
					+"where tb.linkId is not null and date_format(tc.add_date,'%Y-%m-%d %H') = ? "
					+"group by tb.province, tb.isfilter";
					
		Object[] params = new Object[]{time_begin, time_end, sp_number, lower_id, time_begin, time_end, sp_number, lower_id, add_date};
		logger.info(String.format("根据查询条件获得数据分析信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<AnalysisVo> analysisVoList = this.getJdbcTemplate().query(sql, params, tempMapper);
		if(analysisVoList != null && analysisVoList.size() > 0){
			return analysisVoList;
		}
		
		return null;
	}
	/**根据查询条件获得数据分析信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<AnalysisVo> getProvinceAnalysisPartner(String sp_number, int user_id, String time_begin, String time_end, String add_date) {
		String sql = "select tb.province, tb.isfilter, count(1) fee_user_num, sum(tb.minute) fee_minute, " 
					+"ceiling(sum(case when tb.fee_type = 1 then (tb.minute * tb.fee_code/100) when tb.fee_type = 2 then (tb.fee_code/100) else 0 end)) fee_price "
					+"from ("
					+" select tc.add_date, tc.linkId "
					+" from tb_ivr_user_callin tc "
					+" inner join tb_ivr_config_router tr on tr.sp_number = tc.sp_number "
					+" inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id "
					+" where tc.add_date >= ? and tc.add_date <= ? and tc.sp_number = ? and tp.user_id = ? and tc.isfilter = 0 "
					+") tc "
					+"left join ("
					+" select tb.province, tb.isfilter, tb.minute, tcb.fee_type, tcb.fee_code, tb.linkId "
					+" from tb_ivr_user_bill tb "
					+" inner join tb_ivr_config_router tr on tr.id = tb.router_id "
					+" inner join tb_ivr_config_lower_partner tp on tp.id = tr.lower_id "
					+" inner join tb_ivr_config_business tcb on tcb.id = tr.sp_code_id "
					+" where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 "
					+") tb "
					+"on tb.linkId = tc.linkId "
					+"where tb.linkId is not null and date_format(tc.add_date,'%Y-%m-%d %H') = ? "
					+"group by tb.province, tb.isfilter";
					
		Object[] params = new Object[]{time_begin, time_end, sp_number, user_id, time_begin, time_end, sp_number, user_id, add_date};
		logger.info(String.format("根据查询条件获得数据分析信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<AnalysisVo> analysisVoList = this.getJdbcTemplate().query(sql, params, tempMapper);
		if(analysisVoList != null && analysisVoList.size() > 0){
			return analysisVoList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<AnalysisVo> tempMapper = new ParameterizedRowMapper<AnalysisVo>() {
		@Override
		public AnalysisVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnalysisVo analysisVo = new AnalysisVo();
			analysisVo.setProvince(rs.getString("province"));
			analysisVo.setIsfilter(rs.getInt("isfilter"));
			analysisVo.setFee_user_num(rs.getInt("fee_user_num"));
			analysisVo.setFee_minute(rs.getInt("fee_minute"));
			analysisVo.setFee_price(rs.getInt("fee_price"));
			return analysisVo;
		}
	};
	/**根据查询条件获得挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getHangupRateList(String sp_number, String time_begin) {
		String start_date = time_begin + " 00:00:00";
		String end_date = time_begin + " 23:59:59";
		List<Object> paramList = new ArrayList<Object>();
		
		String sql = "select a.sp_number, a.add_date, a.user_num prv_fifty, b.user_num prv_ten, c.user_num prv_five " 
					+"from ("
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 50 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") a "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
					sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
					sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 10 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") b "
					+"on b.sp_number = a.sp_number "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 5 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") c "
					+"on c.sp_number = a.sp_number ";
					
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, hangupRateMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		
		return null;
	}
	/**根据查询条件获得挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getHangupRateListPartner(String sp_number, String time_begin, int user_id) {
		String start_date = time_begin + " 00:00:00";
		String end_date = time_begin + " 23:59:59";
		List<Object> paramList = new ArrayList<Object>();
		
		String sql = "select a.sp_number, a.add_date, a.user_num prv_fifty, b.user_num prv_ten, c.user_num prv_five " 
					+"from ("
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += " and tp.user_id = ? and tb.isfilter = 0 ";
				paramList.add(user_id);
				
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"    	inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += " and tp.user_id = ? and tb.isfilter = 0 ";
				paramList.add(user_id);
					
				sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 50 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") a "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
					sql += " and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(user_id);
					sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"    	inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
					sql += " and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(user_id);
					sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 10 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") b "
					+"on b.sp_number = a.sp_number "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.second, tub1.user_num, tub2.rowNum "
					+"  from (" 
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
				sql += " and tp.user_id = ? and tb.isfilter = 0 ";
				paramList.add(user_id);
				
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"   order by tb.sp_number, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"    select if(@spnumber = tubr.sp_number, @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @spnumber := tubr.sp_number, tubr.sp_number, tubr.second "
					+"    from ( "
					+" 	    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num, @rowNum := 1, @spnumber := '' "
					+"      from tb_ivr_user_bill tb "
					+"    	inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"      where tb.add_date >= ? and tb.add_date <= ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					if(!"".equals(sp_number)){
						sql += "and tb.sp_number = ? ";
						paramList.add(sp_number);
					}
					sql += " and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(user_id);
				sql += "    group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second "
					+"      order by tb.sp_number, user_num desc "
					+"    ) tubr "
					+"  ) tub2 on tub2.sp_number = tub1.sp_number and tub2.second = tub1.second "
					+" where tub2.rowNum <= 5 "
					+" ) tbm "
					+" group by tbm.sp_number, tbm.add_date "
					+") c "
					+"on c.sp_number = a.sp_number ";
					
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, hangupRateMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		
		return null;
	}
	private ParameterizedRowMapper<HangupRateVo> hangupRateMapper = new ParameterizedRowMapper<HangupRateVo>() {
		@Override
		public HangupRateVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			HangupRateVo hangupRateVo = new HangupRateVo();
			hangupRateVo.setSp_number(rs.getString("sp_number"));
			hangupRateVo.setAdd_date(rs.getString("add_date"));
			hangupRateVo.setPrv_fifty(rs.getInt("prv_fifty"));
			hangupRateVo.setPrv_ten(rs.getInt("prv_ten"));
			hangupRateVo.setPrv_five(rs.getInt("prv_five"));
			return hangupRateVo;
		}
	};
	/**根据条件查询分省挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getProvinceHangupRateVo(String sp_number, String time_begin) {
		String start_date = time_begin + " 00:00:00";
		String end_date = time_begin + " 23:59:59";
		List<Object> paramList = new ArrayList<Object>();
		
		String sql = "select a.sp_number, a.add_date, a.province, a.user_num prv_fifty, b.user_num prv_ten, c.user_num prv_five " 
					+"from ("
				
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 50 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") a "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 10 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") b "
					+"on b.sp_number = a.sp_number and b.province = a.province "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 5 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") c "
					+"on c.sp_number = a.sp_number and c.province = a.province ";
					
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, provinceMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		
		return null;
	}
	/**根据条件查询分省挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getProvinceHangupRateVoPartner(String sp_number, String time_begin, int user_id) {
		String start_date = time_begin + " 00:00:00";
		String end_date = time_begin + " 23:59:59";
		List<Object> paramList = new ArrayList<Object>();
		
		String sql = "select a.sp_number, a.add_date, a.province, a.user_num prv_fifty, b.user_num prv_ten, c.user_num prv_five " 
					+"from ("
				
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 50 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") a "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 10 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") b "
					+"on b.sp_number = a.sp_number and b.province = a.province "
					+"inner join "
					+"( "
					+" select tbm.sp_number, tbm.add_date, tbm.province, sum(tbm.user_num) user_num "
					+" from ("
					+"  select tub1.sp_number, tub1.add_date, tub1.province, tub1.second, tub1.user_num, tub2.rowNum " 
					+"  from ( "
					+"   select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num "
					+"   from tb_ivr_user_bill tb " 
					+"   inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"   where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
					
				sql += " group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+"   order by tb.province, user_num desc "
					+"  ) tub1 "
					+"  inner join ( "
					+"   select if(CONVERT(@province USING utf8) = CONVERT(tubr.province USING utf8), @rowNum := @rowNum + 1, @rowNum := 1) rowNum, @province := tubr.province, tubr.province, tubr.second "
					+"   from ( "
					+"    select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.province, tb.second, count(0) user_num, @rowNum := 1, @province := '' "
					+"    from tb_ivr_user_bill tb "
					+"    inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id "
					+"    where tb.add_date >= ? and tb.add_date <= ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 ";
					paramList.add(start_date);
					paramList.add(end_date);
					paramList.add(sp_number);
					paramList.add(user_id);
				sql += "  group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.province, tb.second "
					+ "  order by tb.province,user_num desc "
					+ " ) tubr "
					+")  tub2 on tub2.province = tub1.province and tub2.second = tub1.second "
					+"where tub2.rowNum <= 5 "
					+") tbm "
					+"group by tbm.sp_number, tbm.add_date, tbm.province "
					+") c "
					+"on c.sp_number = a.sp_number and c.province = a.province ";
					
		Object[] params = paramList.toArray();
		logger.info(String.format("根据查询条件获得挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, provinceMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		
		return null;
	}
	private ParameterizedRowMapper<HangupRateVo> provinceMapper = new ParameterizedRowMapper<HangupRateVo>() {
		@Override
		public HangupRateVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			HangupRateVo hangupRateVo = new HangupRateVo();
			hangupRateVo.setSp_number(rs.getString("sp_number"));
			hangupRateVo.setAdd_date(rs.getString("add_date"));
			hangupRateVo.setProvince(rs.getString("province"));
			hangupRateVo.setPrv_fifty(rs.getInt("prv_fifty"));
			hangupRateVo.setPrv_ten(rs.getInt("prv_ten"));
			hangupRateVo.setPrv_five(rs.getInt("prv_five"));
			return hangupRateVo;
		}
	};
	/**根据条件查询挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getDetailHangupRateVo(String sp_number, String time_begin) {
		String sql = "select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num " +
					 "from tb_ivr_user_bill tb " +
					 "where date_format(tb.add_date,'%Y-%m-%d') = ? and tb.sp_number = ? " +
					 "group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second " +
					 "order by user_num desc " +
					 "limit 50 ";
		Object[] params= new Object[]{time_begin, sp_number};
		logger.info(String.format("根据条件查询挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, detailMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		return null;
	}
	
	/**根据条件查询挂断率信息VO列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<HangupRateVo> getDetailHangupRateVoPartner(String sp_number, String time_begin, int user_id) {
		String sql = "select tb.sp_number, date_format(tb.add_date,'%Y-%m-%d') add_date, tb.second, count(0) user_num " +
					 "from tb_ivr_user_bill tb " +
					 "inner join tb_ivr_config_lower_partner tp on tp.id = tb.lower_id " +
					 "where date_format(tb.add_date,'%Y-%m-%d') = ? and tb.sp_number = ? and tp.user_id = ? and tb.isfilter = 0 " +
					 "group by tb.sp_number, date_format(tb.add_date,'%Y-%m-%d'), tb.second " +
					 "order by user_num desc " +
					 "limit 50 ";
		Object[] params= new Object[]{time_begin, sp_number, user_id};
		logger.info(String.format("根据条件查询挂断率信息VO列表[%s]", BaseUtil.logSQL(sql, params)));
		List<HangupRateVo> hangupRateVoList = this.getJdbcTemplate().query(sql, params, detailMapper);
		if(hangupRateVoList != null && hangupRateVoList.size() > 0){
			return hangupRateVoList;
		}
		return null;
	}
	
	private ParameterizedRowMapper<HangupRateVo> detailMapper = new ParameterizedRowMapper<HangupRateVo>() {
		@Override
		public HangupRateVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			HangupRateVo hangupRateVo = new HangupRateVo();
			hangupRateVo.setSp_number(rs.getString("sp_number"));
			hangupRateVo.setAdd_date(rs.getString("add_date"));
			hangupRateVo.setSecond(rs.getInt("second"));
			hangupRateVo.setUser_num(rs.getInt("user_num"));
			
			return hangupRateVo;
		}
	};
	
}
